using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.Alf.Site.Entidades;

namespace MakeIT.Alf.Site.Negocios
{
	public partial class AlfDAO : DAO
	{

		public static ImovelcatVO getImovelcat(int? idImovelCat)
		{
			ImovelcatVO imovelcatVO = null;
			List<ImovelcatVO> listImovelcatVOs = getImovelcats(idImovelCat, null, null);
			if (listImovelcatVOs != null && listImovelcatVOs.Count > 0)
			{
				imovelcatVO = listImovelcatVOs[0];
			}
			return imovelcatVO;
		}

		public static List<ImovelcatVO> getImovelcats()
		{
			return getImovelcats(null, null, null);
		}

		public static List<ImovelcatVO> getImovelcats(int? idImovelCat)
		{
			return getImovelcats(idImovelCat, null, null);
		}

		public static List<ImovelcatVO> getImovelcats(int? idImovelCat, int? idImovelTipo, string imovelCat)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT imvc.idImovelCat as imvc_idImovelCat, imvc.imovelCat as imvc_imovelCat, imvc.idImovelTipo as imvc_idImovelTipo, imvt.imovelTipo as imvt_imovelTipo, imvc.Cadastro as imvc_Cadastro, imvc.CadastradoPor as imvc_CadastradoPor, imvc.Edicao as imvc_Edicao, imvc.EditadoPor as imvc_EditadoPor  ");
			query.AppendLine("FROM imovelcat as imvc LEFT OUTER JOIN imoveltipo as imvt on imvc.idImovelTipo=imvt.idImovelTipo  ");
			if (idImovelCat != null)
			{
				query.AppendLine(where + "imvc.idImovelCat=@idImovelCat ");
				where = "AND ";
			}
			if (idImovelTipo != null)
			{
				query.AppendLine(where + "imvc.idImovelTipo=@idImovelTipo ");
				where = "AND ";
			}
            if (imovelCat != null)
            {
                query.AppendLine(where + "imvt.imovelCat LIKE @imovelCat ");
                where = "AND ";
            }


			query.AppendLine("ORDER BY imvc.idImovelCat ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			if (idImovelCat!= null) cmd.Parameters.Add(getParameter(cmd, "@idImovelCat", idImovelCat));
			if (idImovelTipo!= null) cmd.Parameters.Add(getParameter(cmd, "@idImovelTipo", idImovelTipo));
            if (imovelCat != null) cmd.Parameters.Add(getParameter(cmd, "@imovelCat", "%" + imovelCat + "%"));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<ImovelcatVO> listImovelcat = new List<ImovelcatVO>();

				while (reader.Read())
				{
					ImovelcatVO imovelcatVO = ImovelcatVO.CreateVO();
					imovelcatVO.idImovelCat = reader.GetInt32(reader.GetOrdinal("imvc_idImovelCat"));
					imovelcatVO.imovelCat = reader.GetString(reader.GetOrdinal("imvc_imovelCat"));
					imovelcatVO.idImovelTipo = reader.GetInt32(reader.GetOrdinal("imvc_idImovelTipo"));
					if (!reader.IsDBNull(reader.GetOrdinal("imvt_imovelTipo")))
						imovelcatVO.idImovelTipoVO.imovelTipo = reader.GetString(reader.GetOrdinal("imvt_imovelTipo"));
					imovelcatVO.cadastro = reader.GetDateTime(reader.GetOrdinal("imvc_Cadastro"));
					imovelcatVO.cadastradoPor = reader.GetString(reader.GetOrdinal("imvc_CadastradoPor"));
					imovelcatVO.edicao = reader.GetDateTime(reader.GetOrdinal("imvc_Edicao"));
					imovelcatVO.editadoPor = reader.GetString(reader.GetOrdinal("imvc_EditadoPor"));


					listImovelcat.Add(imovelcatVO);
				}
				reader.Close();
				return listImovelcat;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }
		}

		public static void setImovelcat(ImovelcatVO imovelcatVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE imovelcat ");
			query.AppendLine("SET imovelCat=@imovelCat, idImovelTipo=@idImovelTipo,  Cadastro=@Cadastro, CadastradoPor=@CadastradoPor, Edicao=@Edicao, EditadoPor=@EditadoPor ");
			query.AppendLine("WHERE idImovelCat=@idImovelCat ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idImovelCat", imovelcatVO.idImovelCat));
			cmd.Parameters.Add(getParameter(cmd, "@imovelCat", imovelcatVO.imovelCat));
			cmd.Parameters.Add(getParameter(cmd, "@idImovelTipo", imovelcatVO.idImovelTipo));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", imovelcatVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", imovelcatVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", imovelcatVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", imovelcatVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}

		public static long newImovelcat(ImovelcatVO imovelcatVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO imovelcat ");
			query.AppendLine("  ( imovelCat, idImovelTipo, Cadastro, CadastradoPor, Edicao, EditadoPor ) ");
			query.AppendLine("VALUES ( @imovelCat, @idImovelTipo, @Cadastro, @CadastradoPor, @Edicao, @EditadoPor ) ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@imovelCat", imovelcatVO.imovelCat));
			cmd.Parameters.Add(getParameter(cmd, "@idImovelTipo", imovelcatVO.idImovelTipo));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", imovelcatVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", imovelcatVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", imovelcatVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", imovelcatVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('imovelcat') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}

		public static void delImovelcat(ImovelcatVO imovelcatVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM imovelcat ");
			query.AppendLine("WHERE idImovelCat=@idImovelCat ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idImovelCat", imovelcatVO.idImovelCat));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}
		public static int getCountImovelcatxIdImovelTipo(int? idImovelTipo)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idImovelTipo) as ocorrencias FROM imovelcat WHERE IdImovelTipo=@IdImovelTipo ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdImovelTipo", idImovelTipo));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }
		}
	}
}
